package cn.virens.common.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;

import com.alibaba.druid.pool.DruidDataSource;

import cn.virens.common.CloseableUtil;

public class DatabaseConnoction implements AutoCloseable {
	private final Connection connection;

	private List<Statement> statements = null;
	private List<ResultSet> resultSets = null;

	protected DatabaseConnoction(Connection connection) {
		this.connection = connection;
	}

	protected DatabaseConnoction(DruidDataSource dataSource) throws SQLException {
		this(dataSource.getConnection());
	}

	protected Connection connection() {
		return connection;
	}

	/**
	 * 处理字符串参数,前后追加单引号
	 * 
	 * @param  value
	 * @return
	 */
	public String value(String value) {
		if (value == null) {
			return "null";
		} else if (value.isEmpty()) {
			return "''";
		} else {
			return "'" + value.replaceAll("'", "\\'") + "'";
		}
	}

	/**
	 * 创建 call 方式 的查询声明
	 * 
	 * @param  sql
	 * @return
	 * @throws SQLException
	 */
	public CallableStatement prepareCall(String sql) throws SQLException {
		return addStatement(connection.prepareCall(sql));
	}

	/**
	 * 创建查询声明
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Statement createStatement() throws SQLException {
		return addStatement(connection.createStatement());
	}

	/**
	 * 创建查询声明
	 * 
	 * @param  sql
	 * @return
	 * @throws SQLException
	 */
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return addStatement(connection.prepareStatement(sql));
	}

	/**
	 * 创建查询声明
	 * 
	 * @param  format
	 * @param  args
	 * @return
	 * @throws SQLException
	 */
	public PreparedStatement prepareStatement(String format, Object... args) throws SQLException {
		return prepareStatement(String.format(format, args));
	}

	/**
	 * 添加被管理的查询声明
	 * 
	 * @param  stm
	 * @return
	 */
	protected <S extends Statement> S addStatement(S stm) {
		if (statements == null) {
			statements = new ArrayList<>();
		}

		statements.add(stm);

		return stm;
	}

	/**
	 * 执行更新语句
	 * 
	 * @param  sql
	 * @return
	 * @throws SQLException
	 */
	public int executeUpdate(String sql) throws SQLException {
		return executeUpdate(createStatement(), sql);
	}

	/**
	 * 执行更新语句
	 * 
	 * @param  format
	 * @param  args
	 * @return
	 * @throws SQLException
	 */
	public int executeUpdate(String format, Object... args) throws SQLException {
		return executeUpdate(String.format(format, args));
	}

	/**
	 * 执行更新语句
	 * 
	 * @param  stm
	 * @param  sql
	 * @return
	 * @throws SQLException
	 */
	public int executeUpdate(Statement stm, String sql) throws SQLException {
		return stm.executeUpdate(sql);
	}

	/**
	 * 执行查询语句
	 * 
	 * @param  sql
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql) throws SQLException {
		return executeQuery(createStatement(), sql);
	}

	/**
	 * 执行查询语句
	 * 
	 * @param  format
	 * @param  args
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String format, Object... args) throws SQLException {
		return executeQuery(String.format(format, args));
	}

	/**
	 * 执行查询语句
	 * 
	 * @param  stm
	 * @param  sql
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(Statement stm, String sql) throws SQLException {
		return addResultSet(stm.executeQuery(sql));
	}

	/**
	 * 执行查询语句，并对结果进行循环取值
	 * 
	 * @param  sql
	 * @param  consumer
	 * @throws SQLException
	 */
	public void executeQuery(String sql, Consumer<ResultSet> consumer) throws SQLException {
		ResultSet result = executeQuery(sql);

		while (result.next()) {
			consumer.accept(result);
		}
	}

	/**
	 * 添加被管理查询返回集
	 * 
	 * @param  result
	 * @return
	 */
	public <R extends ResultSet> R addResultSet(R result) {
		if (resultSets == null) {
			resultSets = new ArrayList<>();
		}

		resultSets.add(result);

		return result;
	}

	/**
	 * 设置是否自动提交
	 * 
	 * @param  autoCommit
	 * @throws SQLException
	 */
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		connection.setAutoCommit(autoCommit);
	}

	/**
	 * 事务提交
	 * 
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		connection.commit();
	}

	/**
	 * 事务回滚
	 * 
	 * @throws SQLException
	 */
	public void rollback() throws SQLException {
		connection.rollback();
	}

	public boolean isClosed() throws SQLException {
		return connection.isClosed();
	}

	/**
	 * 关闭连接
	 * 
	 * @throws SQLException
	 */
	@Override
	public void close() {
		CloseableUtil.closes(resultSets);// 关闭查询返回集
		CloseableUtil.closes(statements);// 关闭查询声明
		CloseableUtil.close(connection);// 关闭连接
	}
}
